using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using iBeauty.BusinessObjects;

namespace iBeauty.DAL
{
	public class BillDAO
	{
		public BillDAO()
		{
			DbProviderHelper.GetConnection();
		}
		public List<Bill> GetBills()
		{
			try
			{
				List<Bill> lstBills = new List<Bill>();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [ID],[CreateDate],[ModifiedDate],[TimeStamp],[BillCode],[BillDate],[SupplierCode],[SupplierName],[ShopCode],[ShopName],[StaffCode],[StaffName],[BillType],[PaymentType],[QuantityTotal],[TaxRate],[MoneyTotalNoTax],[TaxTotal],[MoneyTotal],[PayTotal],[DebtTotal],[AccountId],[Note] FROM [Bill]",CommandType.Text);
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					Bill oBill = new Bill();
					oBill.ID = Convert.ToString(oDbDataReader["ID"]);

					if(oDbDataReader["CreateDate"] != DBNull.Value)
						oBill.CreateDate = Convert.ToDateTime(oDbDataReader["CreateDate"]);

					if(oDbDataReader["ModifiedDate"] != DBNull.Value)
						oBill.ModifiedDate = Convert.ToDateTime(oDbDataReader["ModifiedDate"]);

					if(oDbDataReader["TimeStamp"] != DBNull.Value)
						oBill.TimeStamp = Convert.ToDateTime(oDbDataReader["TimeStamp"]);

					if(oDbDataReader["BillCode"] != DBNull.Value)
						oBill.BillCode = Convert.ToString(oDbDataReader["BillCode"]);

					if(oDbDataReader["BillDate"] != DBNull.Value)
						oBill.BillDate = Convert.ToDateTime(oDbDataReader["BillDate"]);

					if(oDbDataReader["SupplierCode"] != DBNull.Value)
						oBill.SupplierCode = Convert.ToString(oDbDataReader["SupplierCode"]);

					if(oDbDataReader["SupplierName"] != DBNull.Value)
						oBill.SupplierName = Convert.ToString(oDbDataReader["SupplierName"]);

					if(oDbDataReader["ShopCode"] != DBNull.Value)
						oBill.ShopCode = Convert.ToString(oDbDataReader["ShopCode"]);

					if(oDbDataReader["ShopName"] != DBNull.Value)
						oBill.ShopName = Convert.ToString(oDbDataReader["ShopName"]);

					if(oDbDataReader["StaffCode"] != DBNull.Value)
						oBill.StaffCode = Convert.ToString(oDbDataReader["StaffCode"]);

					if(oDbDataReader["StaffName"] != DBNull.Value)
						oBill.StaffName = Convert.ToString(oDbDataReader["StaffName"]);

					if(oDbDataReader["BillType"] != DBNull.Value)
						oBill.BillType = Convert.ToString(oDbDataReader["BillType"]);

					if(oDbDataReader["PaymentType"] != DBNull.Value)
						oBill.PaymentType = Convert.ToString(oDbDataReader["PaymentType"]);

					if(oDbDataReader["QuantityTotal"] != DBNull.Value)
						oBill.QuantityTotal = Convert.ToString(oDbDataReader["QuantityTotal"]);

					if(oDbDataReader["TaxRate"] != DBNull.Value)
						oBill.TaxRate = Convert.ToDouble(oDbDataReader["TaxRate"]);

					if(oDbDataReader["MoneyTotalNoTax"] != DBNull.Value)
						oBill.MoneyTotalNoTax = Convert.ToDouble(oDbDataReader["MoneyTotalNoTax"]);

					if(oDbDataReader["TaxTotal"] != DBNull.Value)
						oBill.TaxTotal = Convert.ToDouble(oDbDataReader["TaxTotal"]);

					if(oDbDataReader["MoneyTotal"] != DBNull.Value)
						oBill.MoneyTotal = Convert.ToDouble(oDbDataReader["MoneyTotal"]);

					if(oDbDataReader["PayTotal"] != DBNull.Value)
						oBill.PayTotal = Convert.ToDouble(oDbDataReader["PayTotal"]);

					if(oDbDataReader["DebtTotal"] != DBNull.Value)
						oBill.DebtTotal = Convert.ToDouble(oDbDataReader["DebtTotal"]);

					if(oDbDataReader["AccountId"] != DBNull.Value)
						oBill.AccountId = Convert.ToInt32(oDbDataReader["AccountId"]);

					if(oDbDataReader["Note"] != DBNull.Value)
						oBill.Note = Convert.ToString(oDbDataReader["Note"]);
					lstBills.Add(oBill);
				}
				oDbDataReader.Close();
				return lstBills;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public Bill GetBill(string ID)
		{
			try
			{
				Bill oBill = new Bill();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [ID],[CreateDate],[ModifiedDate],[TimeStamp],[BillCode],[BillDate],[SupplierCode],[SupplierName],[ShopCode],[ShopName],[StaffCode],[StaffName],[BillType],[PaymentType],[QuantityTotal],[TaxRate],[MoneyTotalNoTax],[TaxTotal],[MoneyTotal],[PayTotal],[DebtTotal],[AccountId],[Note] FROM [Bill] WHERE [ID]=@ID",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,ID));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oBill.ID = Convert.ToString(oDbDataReader["ID"]);

					if(oDbDataReader["CreateDate"] != DBNull.Value)
						oBill.CreateDate = Convert.ToDateTime(oDbDataReader["CreateDate"]);

					if(oDbDataReader["ModifiedDate"] != DBNull.Value)
						oBill.ModifiedDate = Convert.ToDateTime(oDbDataReader["ModifiedDate"]);

					if(oDbDataReader["TimeStamp"] != DBNull.Value)
						oBill.TimeStamp = Convert.ToDateTime(oDbDataReader["TimeStamp"]);

					if(oDbDataReader["BillCode"] != DBNull.Value)
						oBill.BillCode = Convert.ToString(oDbDataReader["BillCode"]);

					if(oDbDataReader["BillDate"] != DBNull.Value)
						oBill.BillDate = Convert.ToDateTime(oDbDataReader["BillDate"]);

					if(oDbDataReader["SupplierCode"] != DBNull.Value)
						oBill.SupplierCode = Convert.ToString(oDbDataReader["SupplierCode"]);

					if(oDbDataReader["SupplierName"] != DBNull.Value)
						oBill.SupplierName = Convert.ToString(oDbDataReader["SupplierName"]);

					if(oDbDataReader["ShopCode"] != DBNull.Value)
						oBill.ShopCode = Convert.ToString(oDbDataReader["ShopCode"]);

					if(oDbDataReader["ShopName"] != DBNull.Value)
						oBill.ShopName = Convert.ToString(oDbDataReader["ShopName"]);

					if(oDbDataReader["StaffCode"] != DBNull.Value)
						oBill.StaffCode = Convert.ToString(oDbDataReader["StaffCode"]);

					if(oDbDataReader["StaffName"] != DBNull.Value)
						oBill.StaffName = Convert.ToString(oDbDataReader["StaffName"]);

					if(oDbDataReader["BillType"] != DBNull.Value)
						oBill.BillType = Convert.ToString(oDbDataReader["BillType"]);

					if(oDbDataReader["PaymentType"] != DBNull.Value)
						oBill.PaymentType = Convert.ToString(oDbDataReader["PaymentType"]);

					if(oDbDataReader["QuantityTotal"] != DBNull.Value)
						oBill.QuantityTotal = Convert.ToString(oDbDataReader["QuantityTotal"]);

					if(oDbDataReader["TaxRate"] != DBNull.Value)
						oBill.TaxRate = Convert.ToDouble(oDbDataReader["TaxRate"]);

					if(oDbDataReader["MoneyTotalNoTax"] != DBNull.Value)
						oBill.MoneyTotalNoTax = Convert.ToDouble(oDbDataReader["MoneyTotalNoTax"]);

					if(oDbDataReader["TaxTotal"] != DBNull.Value)
						oBill.TaxTotal = Convert.ToDouble(oDbDataReader["TaxTotal"]);

					if(oDbDataReader["MoneyTotal"] != DBNull.Value)
						oBill.MoneyTotal = Convert.ToDouble(oDbDataReader["MoneyTotal"]);

					if(oDbDataReader["PayTotal"] != DBNull.Value)
						oBill.PayTotal = Convert.ToDouble(oDbDataReader["PayTotal"]);

					if(oDbDataReader["DebtTotal"] != DBNull.Value)
						oBill.DebtTotal = Convert.ToDouble(oDbDataReader["DebtTotal"]);

					if(oDbDataReader["AccountId"] != DBNull.Value)
						oBill.AccountId = Convert.ToInt32(oDbDataReader["AccountId"]);

					if(oDbDataReader["Note"] != DBNull.Value)
						oBill.Note = Convert.ToString(oDbDataReader["Note"]);
				}
				oDbDataReader.Close();
				return oBill;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddBill(string ID,DateTime?CreateDate,DateTime?ModifiedDate,DateTime?TimeStamp,string BillCode,DateTime?BillDate,string SupplierCode,string SupplierName,string ShopCode,string ShopName,string StaffCode,string StaffName,string BillType,string PaymentType,string QuantityTotal,Double?TaxRate,Double?MoneyTotalNoTax,Double?TaxTotal,Double?MoneyTotal,Double?PayTotal,Double?DebtTotal,int?AccountId,string Note)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Bill]([CreateDate],[ModifiedDate],[TimeStamp],[BillCode],[BillDate],[SupplierCode],[SupplierName],[ShopCode],[ShopName],[StaffCode],[StaffName],[BillType],[PaymentType],[QuantityTotal],[TaxRate],[MoneyTotalNoTax],[TaxTotal],[MoneyTotal],[PayTotal],[DebtTotal],[AccountId],[Note])VALUES(@CreateDate,@ModifiedDate,@TimeStamp,@BillCode,@BillDate,@SupplierCode,@SupplierName,@ShopCode,@ShopName,@StaffCode,@StaffName,@BillType,@PaymentType,@QuantityTotal,@TaxRate,@MoneyTotalNoTax,@TaxTotal,@MoneyTotal,@PayTotal,@DebtTotal,@AccountId,@Note)",CommandType.Text);
				if (CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (BillCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,BillCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,DBNull.Value));
				if (BillDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,BillDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,DBNull.Value));
				if (SupplierCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,SupplierCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,DBNull.Value));
				if (SupplierName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,SupplierName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,DBNull.Value));
				if (ShopCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,ShopCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,DBNull.Value));
				if (ShopName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,ShopName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,DBNull.Value));
				if (StaffCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,StaffCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,DBNull.Value));
				if (StaffName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,StaffName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,DBNull.Value));
				if (BillType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,BillType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,DBNull.Value));
				if (PaymentType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,PaymentType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,DBNull.Value));
				if (QuantityTotal!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,QuantityTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,DBNull.Value));
				if (TaxRate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,TaxRate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,DBNull.Value));
				if (MoneyTotalNoTax.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,MoneyTotalNoTax));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,DBNull.Value));
				if (TaxTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,TaxTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,DBNull.Value));
				if (MoneyTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,MoneyTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,DBNull.Value));
				if (PayTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,PayTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,DBNull.Value));
				if (DebtTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,DebtTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,DBNull.Value));
				if (AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));

				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddBill(Bill oBill)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Bill]([CreateDate],[ModifiedDate],[TimeStamp],[BillCode],[BillDate],[SupplierCode],[SupplierName],[ShopCode],[ShopName],[StaffCode],[StaffName],[BillType],[PaymentType],[QuantityTotal],[TaxRate],[MoneyTotalNoTax],[TaxTotal],[MoneyTotal],[PayTotal],[DebtTotal],[AccountId],[Note])VALUES(@CreateDate,@ModifiedDate,@TimeStamp,@BillCode,@BillDate,@SupplierCode,@SupplierName,@ShopCode,@ShopName,@StaffCode,@StaffName,@BillType,@PaymentType,@QuantityTotal,@TaxRate,@MoneyTotalNoTax,@TaxTotal,@MoneyTotal,@PayTotal,@DebtTotal,@AccountId,@Note)",CommandType.Text);
				if (oBill.CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,oBill.CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (oBill.ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,oBill.ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (oBill.TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,oBill.TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (oBill.BillCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,oBill.BillCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,DBNull.Value));
				if (oBill.BillDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,oBill.BillDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,DBNull.Value));
				if (oBill.SupplierCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,oBill.SupplierCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,DBNull.Value));
				if (oBill.SupplierName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,oBill.SupplierName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,DBNull.Value));
				if (oBill.ShopCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,oBill.ShopCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,DBNull.Value));
				if (oBill.ShopName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,oBill.ShopName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,DBNull.Value));
				if (oBill.StaffCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,oBill.StaffCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,DBNull.Value));
				if (oBill.StaffName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,oBill.StaffName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,DBNull.Value));
				if (oBill.BillType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,oBill.BillType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,DBNull.Value));
				if (oBill.PaymentType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,oBill.PaymentType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,DBNull.Value));
				if (oBill.QuantityTotal!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,oBill.QuantityTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,DBNull.Value));
				if (oBill.TaxRate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,oBill.TaxRate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,DBNull.Value));
				if (oBill.MoneyTotalNoTax.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,oBill.MoneyTotalNoTax));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,DBNull.Value));
				if (oBill.TaxTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,oBill.TaxTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,DBNull.Value));
				if (oBill.MoneyTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,oBill.MoneyTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,DBNull.Value));
				if (oBill.PayTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,oBill.PayTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,DBNull.Value));
				if (oBill.DebtTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,oBill.DebtTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,DBNull.Value));
				if (oBill.AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,oBill.AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (oBill.Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,oBill.Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));

				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateBill(string ID,DateTime?CreateDate,DateTime?ModifiedDate,DateTime?TimeStamp,string BillCode,DateTime?BillDate,string SupplierCode,string SupplierName,string ShopCode,string ShopName,string StaffCode,string StaffName,string BillType,string PaymentType,string QuantityTotal,Double?TaxRate,Double?MoneyTotalNoTax,Double?TaxTotal,Double?MoneyTotal,Double?PayTotal,Double?DebtTotal,int?AccountId,string Note)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Bill] SET [CreateDate]=@CreateDate,[ModifiedDate]=@ModifiedDate,[TimeStamp]=@TimeStamp,[BillCode]=@BillCode,[BillDate]=@BillDate,[SupplierCode]=@SupplierCode,[SupplierName]=@SupplierName,[ShopCode]=@ShopCode,[ShopName]=@ShopName,[StaffCode]=@StaffCode,[StaffName]=@StaffName,[BillType]=@BillType,[PaymentType]=@PaymentType,[QuantityTotal]=@QuantityTotal,[TaxRate]=@TaxRate,[MoneyTotalNoTax]=@MoneyTotalNoTax,[TaxTotal]=@TaxTotal,[MoneyTotal]=@MoneyTotal,[PayTotal]=@PayTotal,[DebtTotal]=@DebtTotal,[AccountId]=@AccountId,[Note]=@Note WHERE [ID]=@ID",CommandType.Text);
				if (CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (BillCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,BillCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,DBNull.Value));
				if (BillDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,BillDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,DBNull.Value));
				if (SupplierCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,SupplierCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,DBNull.Value));
				if (SupplierName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,SupplierName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,DBNull.Value));
				if (ShopCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,ShopCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,DBNull.Value));
				if (ShopName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,ShopName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,DBNull.Value));
				if (StaffCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,StaffCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,DBNull.Value));
				if (StaffName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,StaffName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,DBNull.Value));
				if (BillType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,BillType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,DBNull.Value));
				if (PaymentType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,PaymentType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,DBNull.Value));
				if (QuantityTotal!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,QuantityTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,DBNull.Value));
				if (TaxRate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,TaxRate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,DBNull.Value));
				if (MoneyTotalNoTax.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,MoneyTotalNoTax));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,DBNull.Value));
				if (TaxTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,TaxTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,DBNull.Value));
				if (MoneyTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,MoneyTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,DBNull.Value));
				if (PayTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,PayTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,DBNull.Value));
				if (DebtTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,DebtTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,DBNull.Value));
				if (AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateBill(Bill oBill)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Bill] SET [CreateDate]=@CreateDate,[ModifiedDate]=@ModifiedDate,[TimeStamp]=@TimeStamp,[BillCode]=@BillCode,[BillDate]=@BillDate,[SupplierCode]=@SupplierCode,[SupplierName]=@SupplierName,[ShopCode]=@ShopCode,[ShopName]=@ShopName,[StaffCode]=@StaffCode,[StaffName]=@StaffName,[BillType]=@BillType,[PaymentType]=@PaymentType,[QuantityTotal]=@QuantityTotal,[TaxRate]=@TaxRate,[MoneyTotalNoTax]=@MoneyTotalNoTax,[TaxTotal]=@TaxTotal,[MoneyTotal]=@MoneyTotal,[PayTotal]=@PayTotal,[DebtTotal]=@DebtTotal,[AccountId]=@AccountId,[Note]=@Note WHERE [ID]=@ID",CommandType.Text);
				if (oBill.CreateDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,oBill.CreateDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate",DbType.DateTime,DBNull.Value));
				if (oBill.ModifiedDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,oBill.ModifiedDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate",DbType.DateTime,DBNull.Value));
				if (oBill.TimeStamp.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,oBill.TimeStamp));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp",DbType.DateTime,DBNull.Value));
				if (oBill.BillCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,oBill.BillCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillCode",DbType.String,DBNull.Value));
				if (oBill.BillDate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,oBill.BillDate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillDate",DbType.DateTime,DBNull.Value));
				if (oBill.SupplierCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,oBill.SupplierCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierCode",DbType.String,DBNull.Value));
				if (oBill.SupplierName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,oBill.SupplierName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@SupplierName",DbType.String,DBNull.Value));
				if (oBill.ShopCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,oBill.ShopCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopCode",DbType.String,DBNull.Value));
				if (oBill.ShopName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,oBill.ShopName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ShopName",DbType.String,DBNull.Value));
				if (oBill.StaffCode!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,oBill.StaffCode));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffCode",DbType.String,DBNull.Value));
				if (oBill.StaffName!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,oBill.StaffName));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@StaffName",DbType.String,DBNull.Value));
				if (oBill.BillType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,oBill.BillType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BillType",DbType.String,DBNull.Value));
				if (oBill.PaymentType!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,oBill.PaymentType));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PaymentType",DbType.String,DBNull.Value));
				if (oBill.QuantityTotal!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,oBill.QuantityTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@QuantityTotal",DbType.String,DBNull.Value));
				if (oBill.TaxRate.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,oBill.TaxRate));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxRate",DbType.Double,DBNull.Value));
				if (oBill.MoneyTotalNoTax.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,oBill.MoneyTotalNoTax));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotalNoTax",DbType.Double,DBNull.Value));
				if (oBill.TaxTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,oBill.TaxTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TaxTotal",DbType.Double,DBNull.Value));
				if (oBill.MoneyTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,oBill.MoneyTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@MoneyTotal",DbType.Double,DBNull.Value));
				if (oBill.PayTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,oBill.PayTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@PayTotal",DbType.Double,DBNull.Value));
				if (oBill.DebtTotal.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,oBill.DebtTotal));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@DebtTotal",DbType.Double,DBNull.Value));
				if (oBill.AccountId.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,oBill.AccountId));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId",DbType.Int32,DBNull.Value));
				if (oBill.Note!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,oBill.Note));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Note",DbType.String,DBNull.Value));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,oBill.ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveBill(Bill oBill)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Bill] WHERE [ID]=@ID",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,oBill.ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveBill(string ID)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Bill] WHERE [ID]=@ID",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID",DbType.String,ID));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
}
